Visual Analytics Data
Architecture
When you enable Visual Analytics in Poly Lens, Poly deploys a database for you in the Microsoft Azure cloud. Poly Lens populates that database with telemetry events from your Poly Lens account. The database is updated with the latest events once per day. Poly provides you with credentials that allow read-only access to this database. You may then connect your preferred analysis tool to this database to access your telemetry data.
Notes:
- Because the database is updated daily, it will be empty after Visual Analytics is enabled until the first daily update occurs.
- Historical data in Visual Analytics begins at the time that Visual Analytics is enabled. Consequently, it may take some time for a useful amount of historical data to accumulate.
- When devices are deleted from Poly Lens, they are not deleted from Visual Analytics. Instead, they are marked as deleted in the device_status, device_history, and device_events tables. This allows you either include or filter out deleted devices in your historical data, as you prefer.
Data Organization
The telemetry database is organized into star schemas for convenient access from popular BI tools. There are six fact tables. The schema is best understood as six different stars, each with a different fact table at its center. Note: See Schema Diagrams.
Fact Tables
| Item | Description |
|---|---|
| device_status | The device_status table contains one row per device containing the most recent status of the device. |
| device_history | The device_history table contains one row per device per day containing the status of the device at the end of the day. Data older than one year may be purged. |
| device_event | The device_event table contains one row per device status event, containing the device status after the event. Data older than 60 days may be purged. |
| cdr | The CDR table contains records of all calls made by your devices. Each row documents details about a single call. Available data varies by device type. Calls placed on PolyOS devices running in Poly Mode currently have the most data available. Data older than one year may be purged. |
| usage | The usage table contains one row for each detected period of device/room usage, including calls on the device, people in the room, and content sharing to/from the device. When multiple types of usage occur simultaneously, all will be combined into a single usage event. For example, if a person is detected in a room for a one hour period, during which three five minute calls are made, a single usage record will be generated, with a durationSeconds of 3600, a secondsInCall of 900, and a secondsWithPeople of 3600. Usage periods are bounded to a single day, so a meeting lasting from 11:50pm to 12:10am will be recorded as two usages: One from 11:50pm to 12:00am and one from 12:00am to 12:10am. Data older than one year may be purged. |
| media | The media table contains records of media sharing sessions (screen sharing) by your devices. Each row documents details of a single media sharing session. Available data varies by device type. Data older than one year may be purged. |
Dimension Tables
| Item | Description |
|---|---|
| day | The day table is a simple calendar table provided for your convenience. The table contains 31 years of data starting with the year that Visual Analytics is enabled. If you are planning any sophisticated time based aggregations, it is recommended that you substitute your own calendar table that corresponds to your business calendar. |
| device | The device table contains information about devices that is not expected to change over the lifetime of the device. There is one row per device. |
| model | The model table contains hardware model information. The model table does not contain all Poly hardware models, only those of devices you have connected to Lens. Almost every device should have a model. Sometimes Lens does not have model information for unsupported secondary devices. |
| plugin | The plugin table contains softphone plugin information. |
| product | The product table contains product branding information. Product information is not available for all devices. Some devices support multiple types of firmware for the same hardware model, each of which is specific to a different partner integration. These devices will always have product information, and the product information can be used to distinguish the partners/firmwares. |
| room | The room table contains information about the rooms where devices in your account are located. |
| site | The site table contains information about the sites where devices in your account are located. |
| software release | The software_release table contains device firmware release information. Each software release is identified by a software version and a software build. Some devices do not report software release information. |
Data Dictionary
| Item | Description | Example |
|---|---|---|
| answeredInSoftphone | True if the call was answered by a softphone. | True |
| appName | The Provider currently running on the device, such as Poly, 8x8 Meeting Rooms, GoToRoom, Microsoft Teams, RingCentral Rooms, StarLeaf, or Zoom Rooms. | Teams |
| averageCount | The mean number of people detected in the room during the meeting. Only available for devices that support counting people. | 2.3351853 |
| calendarDate | The day expressed as an SQL date. | 2022-01-01T00:00:00:0000000 |
| calendarDay | The day of the week, as text. | Saturday |
| calendarMonth | The month of the year, as text. | January |
| calendarYear | The year. | 2022 |
| callDirection | One of the following: INCOMING, OUTGOING | OUTGOING |
| callid | Unique network signaling identifier for the call. (SIP/H323) | 2127197942 |
| callRate | Bit rate for the call, in kbps. | 1024 |
| connected | Indicates that the device is actively communicating to the cloud. The precise meaning depends on how the device connects to the cloud: - For most audio and video devices, it means that there is an active socket connection to the Poly Lens cloud. - For devices that are only connected via the provisioning flow, it means the device has requested its configuration from the provisioning server recently. - For devices connecting through other devices (such as headsets through Poly Lens Desktop, or an Poly EagleEye Cube through a Poly G7500) it means the device is currently attached AND its parent is actively connected. - For devices that are incapable of connecting to Poly Lens, the value is always null. This includes several types of microphones, cameras, and remote controls attached to video endpoints. | True |
| connectedFlappingCount | A count of the number of times in the day that the device transitioned from connected to disconnected or from disconnected to connected. | 6 |
| dayId | Identifies the day that the data row belongs to. The day is formatted yyyymmdd. It can be used as a foreign key into the day table for making time based aggregations. | 20230216 |
| dayOfMonth | The numeric day of the month (1-31). | 1 |
| dayOfWeek | The numeric day of the week (1-7), with 1 meaning Sunday. | 7 |
| dayOfWeekStartMonday | The numeric day of the week (1-7), with 1 meaning Saturday. | 6 |
| dayOfYear | The numeric day of the year (1-366). | 1 |
| deleted | Indicates whether the device was deleted in the particular context. For the device_status table, this means the device is currently deleted. For the device_history table, this means that the device was deleted on a particular day, and for the device_event table, this means that the device was deleted by the event. | True |
| deviceId | A unique identifier for a device. Although this ID is frequently derived from the serial number or MAC address of the device, there is absolutely no guarantee that it will be meaningful. | 008l2124000675fj |
| dialString | Initial dial string as supplied by the originator. | 4045551212 |
| disconnectInfo | The reason for call termination. | The call has ended. Local user initiated hangup. |
| durationSeconds | The total time (in seconds) that the meeting lasted. | 2700 |
| endTime | The time that the meeting ended. | 2022-01-26T22:00:54.987+0000 |
| externalIP | The public IP address that the device (or relay) used to connect to the Lens cloud. Some devices do not report public IP address. | 73.223.43.53 |
| firstEventTime | The date and time of the first event relating to a device. Note that if the device was connected to Lens before Visual Analytics was enabled, this will be the time that Visual Analytics was enabled. | 2022-09-26T01:26:45.0650000 |
| hardwareRevision | The revision number of the device’s hardware. Some devices do not report revision number. | 1 |
| internalIP | The IP address of the device, as reported by its network interface. Most devices are connected to a private subnet, and for them this is the device's address on that subnet. For devices with multiple IP addresses, the first one in the list is used. Some devices do not report internal IP address. | 192.168.1.9 |
| isBeforeActivation | True if the day occurred before Visual Analytics was activated. | True |
| isLastDayOfMonth | True on the last day of each month. False on all other days. | False |
| isWeekDay | True for Monday, Tuesday, Wednesday, Thursday, and Friday. False for Saturday and Sunday. | False |
| lastEventTime | The date and time that the most recent event relating to a device was received. | 2022-10-20T08:02:29.9540000 |
| localName | The human readable name of the local user. | MyName |
| localTag | SIP signaling tag of the local endpoint. | 9230296C-98C04D83 |
| localUri | SIP/H323 signaling URI of the local endpoint. | SIP:MyName@MyDomain.com |
| macAddress | The MAC address of the device. Some devices do not have a MAC address. Some devices have multiple MAC addresses, and only one is used. Some devices do not report MAC address. | 00:e0:db:5e:9e:40 |
| mediaSharingProtocol | The media sharing protocol, such as App, CAST, HDMI, DrawingBoard, AIRPLAY, or MIRACAST. | HDMI |
| modelId | A unique identifier for a model. A random UUID that was assigned when the model was added to the catalog. | 9e225e0a-a251-4e4f-9f40-b95edc2869c6 |
| monthOfYear | The numeric month of the year (1-12). | 1 |
| peakPeople | The maximum number of people in the room at any one time. Only available for devices that support counting people. | 3 |
| peopleSeconds | The number of people in the room times the number of seconds the room was occupied. For example, if there were ten people in the room for one minute and then two people in the room for three more minutes, peopleSeconds would be (10 x 60) + (2 x 3 x 60) = 960. Only available for devices that support counting people. | 6305 |
| pluginId | ID of softphone plugin used for the call. Foreign key into plugin table. | 4400 |
| productId | A unique identifier for a product. An ID that was assigned when the model was added to the catalog. Product information is not available for all devices. Some devices support multiple types of firmware for the same hardware model, each of which is specific to a different partner integration. These devices will always have product information, and the product information can be used to distinguish the partners/firmwares. | 127 |
| resolution | The video resolution when sharing media formatted as "<width> x<height> " | 1920 x 1080 |
| roomCapacity | The maximum amount of people that the room can hold. | 10 |
| signalingProtocol | The call signaling protocol, such as VOIP, SIP, or H.323. | VOIP |
| qoe_audioprotocolrx | The audio protocol received (such as G.728 or G.722). | SirenLPR |
| qoe_audioprotocoltx | The audio protocol transmitted (such as G.728 or G.722.1). | SirenLPR |
| qoe_avgjitterrx | The average jitter of packets received during a call, calculated from sample tests done once per minute. | 4 |
| qoe_avgjittertx | The average jitter of packets transmitted during a call, calculated from sample tests done once per minute. | 6 |
| qoe_avglatencyrx | The average latency of packets received during a call based on round-trip delay, calculated from sample tests done once per minute. | 20 |
| qoe_avglatencytx | The average latency of packets transmitted during a call based on round-trip delay, calculated from sample tests done once per minute. | 20 |
| qoe_avgpacketslostrx | The number of packets transmitted that were lost during a call. | 26 |
| qoe_avgpacketslosttx | The number of packets received that were lost during a call. | 23 |
| qoe_avgpercentpacketlossrx | The combined average of the percentage of both audio and video packets received that were lost during the five seconds preceding the moment at which a sample was taken. This value does not report a cumulative average for the entire call. However, it does report an average of the sampled values. | 0.03 |
| qoe_avgpercentpacketlosstx | The combined average of the percentage of both audio and video packets transmitted that were lost during the five seconds preceding the moment at which a sample was taken. This value does not report a cumulative average for the entire call. However, it does report an average of the sampled values. | 0.06 |
| qoe_maxjitterrx | The maximum jitter of packets received during a call, calculated from sample tests done once per minute. | 7 |
| qoe_maxjittertx | The maximum jitter of packets transmitted during a call, calculated from sample tests done once per minute. | 12 |
| qoe_maxlatencyrx | The maximum latency for packets received during a call based on round-trip delay, calculated from sample tests done once per minute. | 20 |
| qoe_maxlatencytx | The maximum latency for packets transmitted during a call based on round-trip delay, calculated from sample tests done once per minute. | 20 |
| qoe_precedencelevel | The call priority level assigned to the call. | ROUTINE |
| qoe_totalh320errors | The number of H.320 errors experienced during the call. | 0 |
| qoe_videoformatrx | The video format of the received video. | 1080p |
| qoe_videoformattx | The video format of the transmitted video. | 1080p |
| qoe_videoprotocolrx | The video protocol received (such as H.263 or H.264). | H.264-HP |
| qoe_videoprotocoltx | The video protocol transmitted (such as H.263 or H.264). | H.264-HP |
| relatedDeviceEvent | If applicable, one of the following: - noDevice - talkButtonPress - docked | talkButtonPress |
| remoteName | The human readable name of the remote user. | OtherName |
| remoteTag | SIP signaling tag of the remote endpoint. | 1c1124676033 |
| remoteUri | SIP/H323 signaling URI of the remote endpoint. | SIP:OtherName@SomeDomain.com |
| quarterOfYear | The numeric quarter of the year (1-4). | 1 |
| roomId | A unique identifier for a room. A random UUID that was assigned when the room was created. | 05748aad-f598-4c7b-b165-114d7b5fafc5 |
| secondsInCall | The total time (in seconds) that the device was in a call. | 1497 |
| secondsWithPeople | The total time (in seconds) that people were detected in the room. Only available for devices that support counting people. | 2699 |
| serialNumber | The serial number of the device. | S/N20PF4K |
| siteId | A unique identifier for a site. A random UUID that was assigned when the site was created. | 099fea9d-42a8-487f-bfe2-7080b1fa93ee |
| softphoneVersion | The version of the softphone. | 1.6.0.6754 |
| softwareBuild | The build of software or firmware on the device. This may be necessary to distinguish the software release when a beta test or other pre-release version of software has been installed. Some devices do not report software build information. | 9633 |
| softwareVersion | The software version number. | 2.0.0.001096 |
| startTime | The time that the meeting started. | 2022-01-26T21:15:55.409+0000 |
| user | The name of the user who made the call. | MyDeviceName |
| userAction | If applicable, one of the following: - softphoneUI - deviceEvent | deviceEvent |
| userId | The ID of the user who made the call. | auth0|5eff25b170a2a900141fdbcd |
Schema Diagrams
Device Status
Device History
Device Event
Usage
CDR
Media
